﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using TuongLaiDangCho.DTO;
using System.Data.OleDb;

namespace TuongLaiDangCho.DAO
{
    class QuanLiThoiGianDAO
    {
        public static DataTable LoadChar()
        {
            string sql = "Select * From (select sum(ThoiGian) as HuuIch from QUANLITHOIGIAN where HuuIch='HelpFul') hp,  (select sum(ThoiGian) as KhongHuuIch from QUANLITHOIGIAN where HuuIch='UnHelpful') unhp,(select sum(ThoiGian) as KhongTen from QUANLITHOIGIAN where HuuIch='NoName') nn";
            OleDbConnection cn = DataProvider.OpenCn();
            OleDbDataAdapter da = new OleDbDataAdapter(sql, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            return dt;
        }
        public static DataTable LoadDataChar(QuanLiThoiGianDTO timer)
        {
            string sql = "select * from QUANLITHOIGIAN where NgayThang=#" + timer.NgayThang.ToShortDateString() + "#";
            OleDbConnection cn = DataProvider.OpenCn();
            OleDbDataAdapter da = new OleDbDataAdapter(sql, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public static void InsertDataChar(QuanLiThoiGianDTO timer)
        {
            string sql = "Insert Into QUANLITHOIGIAN(NgayThang,ThoiGian,NoiDung,HuuIch) values(#" + timer.NgayThang.ToShortDateString() + "#," +
                timer.ThoiGian.ToString() + ",'" + timer.NoiDung + "','"+timer.HuuIch+"')";
            OleDbConnection cn = DataProvider.OpenCn();
            OleDbCommand cm = new OleDbCommand(sql, cn);
            cm.ExecuteNonQuery();
            cn.Close();
        }

        public static void UpdateDataChar(QuanLiThoiGianDTO timer)
        {
            string sql = "Update QUANLITHOIGIAN set ThoiGian=" + timer.ThoiGian.ToString() + " , NoiDung='" +
                timer.NoiDung + "' , HuuIch='" + timer.HuuIch + "' where ID="+timer.ID.ToString()+"";
            OleDbConnection cn = DataProvider.OpenCn();
            OleDbCommand cm = new OleDbCommand(sql, cn);
            cm.ExecuteNonQuery();
            cn.Close();
        }

        public static void DeleteDataChar(QuanLiThoiGianDTO timer)
        {
            string sql = "Delete From QUANLITHOIGIAN where ID=" + timer.ID.ToString() + "";
            OleDbConnection cn = DataProvider.OpenCn();
            OleDbCommand cm = new OleDbCommand(sql, cn);
            cm.ExecuteNonQuery();
            cn.Close();
        }
    }
}
